package com.manager.web.common;

import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import com.manager.base.BaseAction;
import com.manager.util.CONSTANT;
import com.manager.util.ExcelUtil;
import com.manager.util.JSONUtil;
import com.manager.util.Log;
import com.manager.util.PageList;
import com.manager.util.Stringbuffer;
import com.opensymphony.xwork2.Action;

public class PageAction extends BaseAction {
	/*
	 * 适用于MySQL的通用分页方法
	 */
	public String listPageForMySQL() {
		String pageActionType = request.getParameter("pageActionType");
		Stringbuffer sb = new Stringbuffer();

		//-------------------------------- 使用displaytag的最普通的分页查询 --------------------------------
		if (pageActionType == null || pageActionType.equals("") || pageActionType.equals("common")) {
			String sqlId = request.getParameter("sqlId");
			int paramTotal = Integer.parseInt(request.getParameter("paramTotal"));
			String forwardUrl = request.getParameter("forwardUrl");

			// 初始化页数
			String pageNumber = request.getParameter("page");
			if (pageNumber == null) {
				pageNumber = "1";
			}

			// 初始化pageList
			PageList pageList = new PageList();
			pageList.setPageNumber(Integer.parseInt(pageNumber));
			pageList.setObjectsPerPage(CONSTANT.PAGE_SIZE);

			// 从数据库中取得相应的sql内容
			Map sqlContent = super.baseService.queryMap("SELECT q.content FROM query_sql q where q.id = ?", sqlId);

			if (sqlContent == null) {
				Log.info("找不到对应的SQL内容，SQLID:" + sqlId);
				return Action.NONE;
			} else {
				sb.append(sqlContent.get("content").toString());
			}
			Log.info("sourceSQL: \n" + sb.toString(), PageAction.class);

			/*
			 * 替换sql中的所有参数
			 * (1)如果可能删除此条件所在行时，必须以一行一个条件
			 * (2)结尾如果不以order by 结尾，必须留一个空行
			 * select * from xxx
			 * where 1 = 1
			 * and name = ''
			 * and age = 22
			 * and hobby LIKE '%movie%'
			 * and name in ('collonn','joanna')
			 * and age in (10,20,30)
			 * order by id desc
			 */
			for (int i = 0; i < paramTotal; i++) {
				String paramName = "_param" + i;
				String paramValue = request.getParameter(paramName);
				if (paramValue != null && !paramValue.equals("")) {
					sb.replace(paramName, paramValue);
					// 保留查询历史用
					request.setAttribute(paramName, paramValue);
				} else {
					sb.deleteLine(paramName);
					// 保留查询历史用
					request.setAttribute(paramName, "");
				}
			}

			// 把最外层的SELECT ... FROM中间的内容替换为 CAST(count(1) AS CHAR) total
			sb.backup().delete(0, sb.indexOf("FROM")).prepend("SElECT count(1) total ");
			Log.info("totalSQL: \n" + sb.toString(), PageAction.class);

			// 设置总记录数
			pageList.setFullListSize(super.baseService.queryInt(sb.toString()));

			// 得到真实想要的分页数据
			sb.reduce().append(" LIMIT ").append(pageList.getOffset()).append(",").append(pageList.getObjectsPerPage());
			Log.info("pageSQL: \n" + sb.toString(), PageAction.class);

			List list = super.baseService.queryList(sb.toString());
			pageList.setList(list);

			request.setAttribute("resultList", pageList);

			super.forward(forwardUrl);
		}
		//-------------------------------- displaytag ajax分页 --------------------------------
		else if (pageActionType.equals("ajax")) {
			String sqlId = request.getParameter("sqlId");
			int paramTotal = Integer.parseInt(request.getParameter("paramTotal"));

			// 初始化页数
			String pageNumber = request.getParameter("page");
			if (pageNumber == null) {
				pageNumber = "1";
			}

			// 初始化pageList
			PageList pageList = new PageList();
			pageList.setPageNumber(Integer.parseInt(pageNumber));
			pageList.setObjectsPerPage(CONSTANT.PAGE_SIZE);

			// 从数据库中取得相应的sql内容
			Map sqlContent = super.baseService.queryMap("SELECT q.content FROM query_sql q where q.id = ?", sqlId);

			if (sqlContent == null) {
				Log.info("找不到对应的SQL内容，SQLID:" + sqlId);
				return Action.NONE;
			} else {
				sb.append(sqlContent.get("content").toString());
			}
			Log.info("sourceSQL: \n" + sb.toString(), PageAction.class);

			/*
			 * 替换sql中的所有参数
			 * (1)如果可能删除此条件所在行时，必须以一行一个条件
			 * (2)结尾如果不以order by 结尾，必须留一个空行
			 * select * from xxx
			 * where 1 = 1
			 * and name = ''
			 * and age = 22
			 * and hobby LIKE '%movie%'
			 * and name in ('collonn','joanna')
			 * and age in (10,20,30)
			 * order by id desc
			 */
			for (int i = 0; i < paramTotal; i++) {
				String paramName = "_param" + i;
				String paramValue = request.getParameter(paramName);
				if (paramValue != null && !paramValue.equals("")) {
					sb.replace(paramName, paramValue);
					// 保留查询历史用
					request.setAttribute(paramName, paramValue);
				} else {
					sb.deleteLine(paramName);
					// 保留查询历史用
					request.setAttribute(paramName, "");
				}
			}

			// 把最外层的SELECT ... FROM中间的内容替换为 CAST(count(1) AS CHAR) total
			sb.backup().delete(0, sb.indexOf("FROM")).prepend("SElECT count(1) total ");
			Log.info("totalSQL: \n" + sb.toString(), PageAction.class);

			// 设置总记录数
			pageList.setFullListSize(super.baseService.queryInt(sb.toString()));

			// 得到真实想要的分页数据
			sb.reduce().append(" LIMIT ").append(pageList.getOffset()).append(",").append(pageList.getObjectsPerPage());
			Log.info("pageSQL: \n" + sb.toString(), PageAction.class);

			List list = super.baseService.queryList(sb.toString());
			Log.info("listSize:" + list.size());
			// 将List<Map>转换成JSON
			StringBuffer rsJSON = JSONUtil.listMapToJSON(list);

			if (rsJSON == null) {
				super.print("null");
				return Action.NONE;
			}

			// 添加参数相关信息
			StringBuffer p = new StringBuffer();
			Map param = request.getParameterMap();
			Iterator it = param.entrySet().iterator();
			while (it.hasNext()) {
				Entry entry = (Entry) it.next();
				if(!entry.getKey().equals("page")){
					p.append(entry.getKey()).append("=").append(String.valueOf(((String[]) entry.getValue())[0])).append("&");
				}
			}
			p.deleteCharAt(p.length() - 1);
			Log.info("params: " + p.toString());

			// 添加分页相关信息
			rsJSON.deleteCharAt(rsJSON.length() - 1);//删除已闭合的"]"
			rsJSON.append(",");
			rsJSON.append("{");
			rsJSON.append("\"pageNumber\":").append(pageList.getPageNumber()).append(",");
			rsJSON.append("\"totalPage\":").append(pageList.getTotalPage()).append(",");
			rsJSON.append("\"objectsPerPage\":").append(pageList.getObjectsPerPage()).append(",");
			rsJSON.append("\"currentListNum\":\"").append(pageList.getCurrentListNum()).append("\",");
			rsJSON.append("\"fullListSize\":").append(pageList.getFullListSize()).append(",");
			rsJSON.append("\"url\":\"").append(request.getRequestURI().substring(1)).append("\",");
			rsJSON.append("\"param\":\"").append(p.toString()).append("\"");
			rsJSON.append("}");

			rsJSON.append("]");

			Log.info("rs Json: " + rsJSON.toString());
			// 输出JSON
			super.print(rsJSON.toString());

		}
		//---------------------------------------- 根据查询条件导出所有的结果集 ----------------------------------------
		else if (pageActionType.equals("exportExcel")) {//
			String sqlId = request.getParameter("sqlId");
			int paramTotal = Integer.parseInt(request.getParameter("paramTotal"));

			// 从数据库中取得相应的sql内容
			Map sqlContent = super.baseService.queryMap("SELECT q.content FROM query_sql q where q.id = ?", sqlId);

			if (sqlContent == null) {
				Log.info("找不到对应的SQL内容，SQLID:" + sqlId);
				return Action.NONE;
			} else {
				sb.append(sqlContent.get("content").toString());
			}
			Log.info("sourceSQL: \n" + sb.toString(), PageAction.class);

			/*
			 * 替换sql中的所有参数
			 * (1)如果可能删除此条件所在行时，必须以一行一个条件
			 * (2)结尾如果不以order by 结尾，必须留一个空行
			 * select * from xxx
			 * where 1 = 1
			 * and name = ''
			 * and age = 22
			 * and hobby LIKE '%movie%'
			 * and name in ('collonn','joanna')
			 * and age in (10,20,30)
			 * order by id desc
			 */
			for (int i = 0; i < paramTotal; i++) {
				String paramName = "_param" + i;
				String paramValue = request.getParameter(paramName);
				if (paramValue != null && !paramValue.equals("")) {
					sb.replace(paramName, paramValue);
					// 保留查询历史用
					request.setAttribute(paramName, paramValue);
				} else {
					sb.deleteLine(paramName);
					// 保留查询历史用
					request.setAttribute(paramName, "");
				}
			}

			List list = super.baseService.queryList(sb.toString());
			if (list != null && list.size() > 0) {
				ExcelUtil.getExcel(list, request, response);
			}
		}

		return Action.NONE;
	}
}
